﻿Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.IO
Imports System.Data.OleDb
Imports System.Data
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
'created by : sum 
'created date: 29/9/2011
'description: Read and write excel function, currently only support dataset export.
Namespace helper
    Public NotInheritable Class ExcelHelper

#Region "Read"

        Private Shared connectionString As String = String.Empty
        Private Shared excelExtention As eExcelExtention = eExcelExtention.xls

        ''' <summary>
        ''' read data from a excel file (xls or xlsx)
        ''' </summary>
        ''' <param name="path">excel file path</param>
        ''' <returns>dataset</returns>
        ''' <remarks></remarks>
        Public Shared Function Read(ByVal path As String) As DataSet
            excelExtention = GetExcelExtention(path)
            Return Read(path, GetWorkSheets(GetConnectionString(path)))
        End Function

        Private Shared Function GetExcelExtention(ByVal path As String) As eExcelExtention
            Dim extention As String = path.Split("."c)(path.Split("."c).Length - 1)
            If extention.Trim() = eExcelExtention.xlsx.ToString() Then
                Return eExcelExtention.xlsx
            Else
                Return eExcelExtention.xls
            End If

        End Function

        Private Shared Function GetConnectionString(ByVal path As String) As String
            If excelExtention = eExcelExtention.xls Then
                connectionString = String.Format("Provider=Microsoft.Jet.OleDb.4.0;data source={0};Extended Properties=Excel 8.0;", path)
            ElseIf excelExtention = eExcelExtention.xlsx Then
                connectionString = String.Format("Provider=Microsoft.ACE.OleDb.12.0; data source={0}; Extended Properties=Excel 8.0;", path)
            End If
            Return connectionString
        End Function

        Private Shared Function Read(ByVal path As String, ByVal workSheets As String()) As DataSet

            Dim ds = New DataSet()
            For Each workSheet As String In workSheets
                Try
                    Using adapter As New OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", workSheet), GetConnectionString(path))

                        adapter.Fill(ds, workSheet)
                    End Using
                Catch ex As Exception
                    Throw New Exception(ex.Message, ex.InnerException)
                End Try
            Next
            Return ds

        End Function

        Private Shared Function GetWorkSheets(ByVal connectionString As String) As String()
            Dim dataTable As DataTable
            Try
                Using connection As New OleDbConnection(connectionString)
                    connection.Open()
                    dataTable = connection.GetSchema("Tables")
                End Using
            Catch ex As Exception
                Throw New Exception(ex.Message, ex.InnerException)
            End Try
            Dim lenght As Integer = dataTable.Rows.Count
            Dim worksheets As String() = New String(lenght - 1) {}
            For i As Integer = 0 To lenght - 1
                worksheets(i) = dataTable.Rows(i)("TABLE_NAME").ToString()
            Next
            Return worksheets
        End Function

        Private Enum eExcelExtention
            xls = 1
            xlsx = 2
        End Enum

#End Region

#Region "Write"
        ''' <summary>
        ''' write a excel file from a dataset
        ''' </summary>
        ''' <param name="dt">datatable</param>
        ''' <param name="path">path to store</param>
        ''' <param name="fileName">file name without extension</param>
        ''' <remarks></remarks>
        Public Shared Sub Write_replaceFileName(ByVal dt As DataTable, ByVal path As String, ByVal fileName As String)

            Dim fileStream As New FileStream(String.Format("{0}\{1}.xls", path, fileName), FileMode.Create)
            Dim writer As New ExcelWriter(fileStream)
            writer.BeginWrite()
            Dim rowIndex As Integer = 0
            For Each column As DataColumn In dt.Columns
                writer.WriteCell(rowIndex, column.Ordinal, column.ColumnName)
            Next
            For Each row As DataRow In dt.Rows
                rowIndex += 1
                For Each column As DataColumn In dt.Columns
                    writer.WriteCell(rowIndex, column.Ordinal, row(column.ColumnName).ToString())

                Next
            Next
            writer.EndWrite()
            fileStream.Close()

        End Sub

        ''' <summary>
        ''' write a excel file from datatable
        ''' </summary>
        ''' <param name="dt">datatable</param>
        ''' <param name="path">path to store</param>
        ''' <remarks></remarks>
        Public Shared Sub Write(ByVal dt As DataTable, ByVal path As String)

            Dim fileStream As New FileStream(String.Format("{0}\{1}.xls", path, dt.TableName), FileMode.Create)
            Dim writer As New ExcelWriter(fileStream)
            writer.BeginWrite()
            Dim rowIndex As Integer = 0
            For Each column As DataColumn In dt.Columns
                writer.WriteCell(rowIndex, column.Ordinal, column.ColumnName)
            Next
            For Each row As DataRow In dt.Rows
                rowIndex += 1
                For Each column As DataColumn In dt.Columns
                    writer.WriteCell(rowIndex, column.Ordinal, row(column.ColumnName).ToString())

                Next
            Next
            writer.EndWrite()
            fileStream.Close()

        End Sub

        ''' <summary>
        ''' write a excel file from dataset
        ''' </summary>
        ''' <param name="ds">dataset</param>
        ''' <param name="path">store path</param>
        ''' <remarks></remarks>
        Public Shared Sub Write(ByVal ds As DataSet, ByVal path As String)
            For Each dt As DataTable In ds.Tables
                Write(dt, path)
            Next

        End Sub

        Private Class ExcelWriter
            Private stream As Stream
            Private writer As BinaryWriter

            Private clBegin As UShort() = {&H809, 8, 0, &H10, 0, 0}
            Private clEnd As UShort() = {&HA, 0}


            Private Sub WriteUshortArray(ByVal value As UShort())
                For i As Integer = 0 To value.Length - 1
                    writer.Write(value(i))
                Next
            End Sub

            Public Sub New(ByVal stream As Stream)
                Me.stream = stream
                writer = New BinaryWriter(stream)
            End Sub

            Public Sub WriteCell(ByVal row As Integer, ByVal col As Integer, ByVal value As String)
                Dim clData As UShort() = {&H204, 0, 0, 0, 0, 0}
                Dim iLen As Integer = value.Length
                Dim plainText As Byte() = Encoding.ASCII.GetBytes(value)
                clData(1) = CUShort(8 + iLen)
                clData(2) = CUShort(row)
                clData(3) = CUShort(col)
                clData(5) = CUShort(iLen)
                WriteUshortArray(clData)
                writer.Write(plainText)
            End Sub

            Public Sub WriteCell(ByVal row As Integer, ByVal col As Integer, ByVal value As Integer)
                Dim clData As UShort() = {&H27E, 10, 0, 0, 0}
                clData(2) = CUShort(row)
                clData(3) = CUShort(col)
                WriteUshortArray(clData)
                Dim iValue As Integer = (value << 2) Or 2
                writer.Write(iValue)
            End Sub

            Public Sub WriteCell(ByVal row As Integer, ByVal col As Integer, ByVal value As Double)
                Dim clData As UShort() = {&H203, 14, 0, 0, 0}
                clData(2) = CUShort(row)
                clData(3) = CUShort(col)
                WriteUshortArray(clData)
                writer.Write(value)
            End Sub

            Public Sub WriteCell(ByVal row As Integer, ByVal col As Integer)
                Dim clData As UShort() = {&H201, 6, 0, 0, &H17}
                clData(2) = CUShort(row)
                clData(3) = CUShort(col)
                WriteUshortArray(clData)
            End Sub

            Public Sub BeginWrite()
                WriteUshortArray(clBegin)
            End Sub

            Public Sub EndWrite()
                WriteUshortArray(clEnd)
                writer.Flush()
            End Sub
        End Class

#End Region

        ''' <summary>
        ''' export excel from a gridview
        ''' </summary>
        ''' <param name="page">page</param>
        ''' <param name="fileName">export file name</param>
        ''' <param name="gv">gridview</param>
        ''' <remarks></remarks>
        Public Shared Sub ExportExcel(ByVal page As Page, ByVal fileName As String, ByVal gv As GridView)

            page.Response.Clear()
            page.Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", fileName))
            page.Response.ContentType = "application/ms-excel"
            Dim sw As StringWriter = New StringWriter
            Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
            '  Create a form to contain the grid
            Dim table As Table = New Table
            table.GridLines = gv.GridLines
            '  add the header row to the table
            If (Not (gv.HeaderRow) Is Nothing) Then
                PrepareControlForExport(gv.HeaderRow)
                table.Rows.Add(gv.HeaderRow)
            End If
            '  add each of the data rows to the table
            For Each row As GridViewRow In gv.Rows
                PrepareControlForExport(row)
                table.Rows.Add(row)
            Next
            '  add the footer row to the table
            If (Not (gv.FooterRow) Is Nothing) Then
                PrepareControlForExport(gv.FooterRow)
                table.Rows.Add(gv.FooterRow)
            End If
            '  render the table into the htmlwriter
            table.RenderControl(htw)
            '  render the htmlwriter into the response
            page.Response.Write(sw.ToString)
            page.Response.End()
        End Sub
        'sum,02052012, allow program to filter out which column won't appear in excel file :D
        ''' <summary>
        ''' export excel from a gridview, allowed filter out which column don't appear in excel file
        ''' </summary>
        ''' <param name="page">page</param>
        ''' <param name="fileName">export file name</param>
        ''' <param name="gv">gridview</param>
        ''' <param name="skipcolumns">index of column that you don't want it appear in your excel file</param>
        ''' <remarks></remarks>
        Public Shared Sub ExportExcel(ByVal page As Page, ByVal fileName As String, ByVal gv As GridView, ByVal skipcolumns() As Long)

            page.Response.Clear()
            page.Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", fileName))
            page.Response.ContentType = "application/ms-excel"
            Dim sw As StringWriter = New StringWriter
            Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
            '  Create a form to contain the grid
            Dim table As Table = New Table
            table.GridLines = gv.GridLines
            '  add the header row to the table
            If (Not (gv.HeaderRow) Is Nothing) Then
                PrepareControlForExport(gv.HeaderRow)
                table.Rows.Add(gv.HeaderRow)
            End If
            '  add each of the data rows to the table
            For Each row As GridViewRow In gv.Rows
                PrepareControlForExport(row)
                table.Rows.Add(row)
            Next
            '  add the footer row to the table
            If (Not (gv.FooterRow) Is Nothing) Then
                PrepareControlForExport(gv.FooterRow)
                table.Rows.Add(gv.FooterRow)
            End If

            'sum, 2/5/2012 filter out column that don't want appear in excel
            ' TableRow tr in tab.Rows
            For Each tr As TableRow In table.Rows

                For Each i As Integer In skipcolumns.OrderByDescending(Function(c) c)
                    If tr.Cells.Count - 1 >= i Then
                        tr.Cells.RemoveAt(i)
                    End If
                Next

            Next


            '  render the table into the htmlwriter
            table.RenderControl(htw)
            '  render the htmlwriter into the response
            'export as unicode
            page.Response.Write("<meta http-equiv=Content-Type content=""text/html; charset=utf-8"">")
            page.Response.Write(sw.ToString)
            page.Response.End()
        End Sub

        ' Replace any of the contained controls with literals
        Private Shared Sub PrepareControlForExport(ByVal control As Control)
            Dim i As Integer = 0
            Do While (i < control.Controls.Count)
                Dim current As Control = control.Controls(i)
                If (TypeOf current Is LinkButton) Then
                    control.Controls.Remove(current)
                    control.Controls.AddAt(i, New LiteralControl(CType(current, LinkButton).Text))
                ElseIf (TypeOf current Is ImageButton) Then
                    control.Controls.Remove(current)
                    control.Controls.AddAt(i, New LiteralControl(CType(current, ImageButton).AlternateText))
                ElseIf (TypeOf current Is HyperLink) Then
                    control.Controls.Remove(current)
                    control.Controls.AddAt(i, New LiteralControl(CType(current, HyperLink).Text))
                ElseIf (TypeOf current Is DropDownList) Then
                    control.Controls.Remove(current)
                    control.Controls.AddAt(i, New LiteralControl(CType(current, DropDownList).SelectedItem.Text))
                ElseIf (TypeOf current Is CheckBox) Then
                    control.Controls.Remove(current)
                    control.Controls.AddAt(i, New LiteralControl(CType(current, CheckBox).Checked))
                    'TODO: Warning!!!, inline IF is not supported ?
                End If
                If current.HasControls Then
                    PrepareControlForExport(current)
                End If
                i = (i + 1)
            Loop
        End Sub
    End Class
End Namespace

